package com.dao;

import com.bean.CSelect;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CSelectDao {
    //获取学生信息列表
    public static List<CSelect> getList() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<CSelect> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("SELECT * FROM cselect");
            rs = stmt.executeQuery();
            while (rs.next()) {
                CSelect cs = new CSelect();
                cs.setStudent_id(rs.getString(1));
                cs.setcourse_id(rs.getString(2));
                cs.setGrades(rs.getString(3));

                list.add(cs);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
        return list;
    }

    //增加学生信息
    public static void addCSelect(CSelect cselect){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("INSERT INTO cselect (student_id,course_id,grades VALUES (?,?,?);");
            stmt.setString(1, cselect.getStudent_id());
            stmt.setString(2, cselect.getCourse_id());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }

    //根据学号查找学生信息
    public static CSelect searchCSelect(CSelect id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rst = null;
        CSelect cs = new CSelect();
        try{
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement("SELECT * FROM cselect WHERE student_id=? and course_id");
            pstmt.setString(1,id);
            rst = pstmt.executeQuery();
            if(rst.next()){
                cs.setStudent_id(rst.getString("student_id"));
                cs.setCourse_id(rst.getString("Course_id"));
                cs.setGrades(rst.getString("Grades"));
                return cs;
            }else{
                return  null;
            }
        }catch(SQLException se){
            return null;
        }finally{
            try{
                conn.close();
            }catch(SQLException se){
            }
        }
    }




    //修改功能
    public static void updateCSelect(CSelect cselect) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("UPDATE cselect SET student_id=?,course_id=?,grades=?,swhere student_id=? and course_id");
            stmt.setString(1, cselect.getStudent_id());
            stmt.setString(2, cselect.getCourse_id());
            stmt.setString(3, cselect.getGrades());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }

    //删除功能
    public static void deleteData(String id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("DELETE FROM student WHERE student_id=? and course_id;");
            stmt.setString(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }
}